package dao;


import com.sun.jdi.PathSearchingVirtualMachine;
import entity.Product;
import entity.Supplier;
import entity.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ProductDao extends BaseDao {
    //列表所有商品信息
    public List getAllProducts() throws Exception {
        List productList = new ArrayList();
        String sql = "select productId,product.categoryId,categoryName,productName,expiryDate,storageLocation,salePrice,purchasePrice,stockQuantity,productPicture" +
                " from product,productCategory where product.categoryId=productCategory.categoryId";
        Connection connection = getConnection();//获取数据库连接
        Statement statement = connection.createStatement();//创建statement对象
        ResultSet resultSet = statement.executeQuery(sql);//执行查询sql
        while(resultSet.next()){
            String productId = resultSet.getString("productId");
            String categoryId = resultSet.getString("categoryId");
            String categoryName = resultSet.getString("categoryName");
            String productName = resultSet.getString("productName");
            String expiryDate = resultSet.getString("expiryDate");
            String storageLocation = resultSet.getString("storageLocation");
            float purchasePrice = resultSet.getFloat("purchasePrice");
            float salePrice = resultSet.getFloat("salePrice");
            int stockQuantity = resultSet.getInt("stockQuantity");
            String productPictuer = resultSet.getString("productPicture");
            Product product = new Product(productId,categoryId,categoryName,productName,expiryDate,storageLocation,purchasePrice,salePrice,stockQuantity,productPictuer);
            productList.add(product);
        }
        return productList;
    }

    //列表指定分类商品信息
    public List getAppointProducts(String categoryIdTemp) throws Exception {
        List productList = new ArrayList();
        String sql = "select productId,product.categoryId,categoryName,productName,expiryDate,storageLocation,salePrice,purchasePrice,stockQuantity,productPicture" +
                " from product,productCategory where product.categoryId=productCategory.categoryId and product.categoryId='" + categoryIdTemp + "'";
        Connection connection = getConnection();//获取数据库连接
        Statement statement = connection.createStatement();//创建statement对象
        ResultSet resultSet = statement.executeQuery(sql);//执行查询sql
        while(resultSet.next()){
            String productId = resultSet.getString("productId");
            String categoryId = resultSet.getString("categoryId");
            String categoryName = resultSet.getString("categoryName");
            String productName = resultSet.getString("productName");
            String expiryDate = resultSet.getString("expiryDate");
            String storageLocation = resultSet.getString("storageLocation");
            float purchasePrice = resultSet.getFloat("purchasePrice");
            float salePrice = resultSet.getFloat("salePrice");
            int stockQuantity = resultSet.getInt("stockQuantity");
            String productPictuer = resultSet.getString("productPicture");
            Product product = new Product(productId,categoryId,categoryName,productName,expiryDate,storageLocation,purchasePrice,salePrice,stockQuantity,productPictuer);
            productList.add(product);
        }
        return productList;
    }

    //查找指定商品信息
    public Product getProduct(String productNameTemp) throws Exception {
        Product product = new Product();
        String sql = "select productId,product.categoryId,categoryName,productName,expiryDate,storageLocation,salePrice,purchasePrice,stockQuantity,productPicture" +
                " from product,productCategory where product.categoryId=productCategory.categoryId and productName='" + productNameTemp + "'";
        Connection connection = getConnection();//获取数据库连接
        Statement statement = connection.createStatement();//创建statement对象
        ResultSet resultSet = statement.executeQuery(sql);//执行查询sql
        while(resultSet.next()){
            String productId = resultSet.getString("productId");
            String categoryId = resultSet.getString("categoryId");
            String categoryName = resultSet.getString("categoryName");
            String productName = resultSet.getString("productName");
            String expiryDate = resultSet.getString("expiryDate");
            String storageLocation = resultSet.getString("storageLocation");
            float purchasePrice = resultSet.getFloat("purchasePrice");
            float salePrice = resultSet.getFloat("salePrice");
            int stockQuantity = resultSet.getInt("stockQuantity");
            String productPictuer = resultSet.getString("productPicture");
            product = new Product(productId,categoryId,categoryName,productName,expiryDate,storageLocation,purchasePrice,salePrice,stockQuantity,productPictuer);
        }
        return product;
    }

    //查找指定商品信息
    public Product getProductName(String productIdTemp) throws Exception {
        Product product = new Product();
        String sql = "select productId,product.categoryId,categoryName,productName,expiryDate,storageLocation,salePrice,purchasePrice,stockQuantity,productPicture" +
                " from product,productCategory where product.categoryId=productCategory.categoryId and product.productId='" + productIdTemp + "'";
        Connection connection = getConnection();//获取数据库连接
        Statement statement = connection.createStatement();//创建statement对象
        ResultSet resultSet = statement.executeQuery(sql);//执行查询sql
        while(resultSet.next()){
            String productId = resultSet.getString("productId");
            String categoryId = resultSet.getString("categoryId");
            String categoryName = resultSet.getString("categoryName");
            String productName = resultSet.getString("productName");
            String expiryDate = resultSet.getString("expiryDate");
            String storageLocation = resultSet.getString("storageLocation");
            float purchasePrice = resultSet.getFloat("purchasePrice");
            float salePrice = resultSet.getFloat("salePrice");
            int stockQuantity = resultSet.getInt("stockQuantity");
            String productPictuer = resultSet.getString("productPicture");
            product = new Product(productId,categoryId,categoryName,productName,expiryDate,storageLocation,purchasePrice,salePrice,stockQuantity,productPictuer);
        }
        return product;
    }

    //录入商品信息
    public boolean addProduct(Product product) throws Exception {
        String sql = "insert into product(productId,categoryId,productName,expiryDate,storageLocation,purchasePrice,salePrice,stockQuantity,productPicture)" +
                " VALUES(?,?,?,?,?,?,?,?,?)";
        Connection connection = getConnection();//获取数据库连接
        PreparedStatement preparedStatement = connection.prepareStatement(sql);//创建PreparedStatement
        preparedStatement.setString(1, product.getProductId());
        //设置第一个？占位符的值
        preparedStatement.setString(2, product.getCategoryId());
        //设置第二个？占位符的值
        preparedStatement.setString(3, product.getProductName());
        //设置第三个？占位符的值
        preparedStatement.setString(4, product.getExpiryDate());
        //设置第四个？占位符的值
        preparedStatement.setString(5, product.getStorageLocation());
        //设置第五个？占位符的值
        preparedStatement.setFloat(6, product.getPurchasePrice());
        //设置第六个？占位符的值
        preparedStatement.setFloat(7, product.getSalePrice());
        //设置第七个？占位符的值
        preparedStatement.setInt(8, product.getStockQuantity());
        //设置第八个？占位符的值
        preparedStatement.setString(9, product.getProductPicture());
        //设置第九个？占位符的值
        int num = preparedStatement.executeUpdate();//执行sql语句
        if (num==1)
        {
            return true;
        }else {
            return false;
        }
    }

    //修改商品信息
    public boolean updateProduct(Product product) {
        int i = 0;
        try{
            Connection connection = getConnection();
            String updatesql = "update product set categoryId=?,"
                    +"productName=?,"
                    +"expiryDate=?,"
                    +"storageLocation=?,"
                    +"purchasePrice=?,"
                    +"salePrice=?,"
                    +"stockQuantity=?,"
                    +"productPicture=?"
                    +" where productId=?";
            PreparedStatement preparedStatement = connection.prepareStatement(updatesql);
            preparedStatement.setString(1,product.getCategoryId());
            preparedStatement.setString(2,product.getProductName());
            preparedStatement.setString(3,product.getExpiryDate());
            preparedStatement.setString(4,product.getStorageLocation());
            preparedStatement.setFloat(5,product.getPurchasePrice());
            preparedStatement.setFloat(6,product.getSalePrice());
            preparedStatement.setInt(7,product.getStockQuantity());
            preparedStatement.setString(8,product.getProductPicture());
            preparedStatement.setString(9,product.getProductId());
            i = preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
        if (i == 1){
            return true;
        }else {
            return false;
        }
    }

    //删除商品信息
    public String deleteProduct(Product product) throws Exception {
        String result= null;
        Connection connection = getConnection();//获取数据库连接

        String sql = "delete from product where productId=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,product.getProductId());
        int num = preparedStatement.executeUpdate();//执行删除sql操作
        if(num==1){
            //删除成功
            result = "删除成功";
        }else {
            //删除失败
            result = "删除失败";
        }
        return result;
    }
    //获取总利润
    public float getProfit() throws Exception {
        String sql = "SELECT totalProfit FROM Profit";
        Connection connection = getConnection();//获取数据库连接
        Statement statement = connection.createStatement();//创建statement对象
        ResultSet resultSet = statement.executeQuery(sql);//执行查询sql
        float totalProfit = 0.0F;
        while(resultSet.next()){
            totalProfit = resultSet.getFloat("totalProfit");
        }
        return totalProfit;
    }
    //获取总销售额
    public float getSaleTotalPrice() throws Exception {
        String sql = "SELECT SUM(saleTotalPrice) FROM Sale";
        Connection connection = getConnection();//获取数据库连接
        Statement statement = connection.createStatement();//创建statement对象
        ResultSet resultSet = statement.executeQuery(sql);//执行查询sql
        float saleRevenue = 0.0F;
        while(resultSet.next()){
            saleRevenue = resultSet.getFloat("SUM(saleTotalPrice)");
        }
        return saleRevenue;
    }

    //获取进货总花费
    public float getPurchaseTotalPrice() throws Exception {
        String sql = "SELECT SUM(purchaseTotalPrice) FROM Purchase";
        Connection connection = getConnection();//获取数据库连接
        Statement statement = connection.createStatement();//创建statement对象
        ResultSet resultSet = statement.executeQuery(sql);//执行查询sql
        float purchaseCost = 0.0F;
        while(resultSet.next()){
            purchaseCost = resultSet.getFloat("SUM(purchaseTotalPrice)");
        }
        return purchaseCost;
    }

    public static void main(String[] args) throws Exception {
        ProductDao productDao = new ProductDao();
        float totalProfit = productDao.getProfit();
        System.out.println(totalProfit);
    }

}
